/**
 * Created by My Company.
 * User: loitran
 * Date: Mar 17, 2012
 * Time: 11:12:57 PM
 * com.mycompany.inventory.persistence.
 */
package com.mycompany.inventory.persistence;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mycompany.common.AbstractDao;
import com.mycompany.common.jdbc.ConnectionDatabase;
import com.mycompany.inventory.pojo.Address;
import com.mycompany.inventory.pojo.AddressOwner;
import com.mycompany.inventory.pojo.CustomInfo;
import com.mycompany.inventory.pojo.Customer;

/**
 * @author loitran
 *
 */
public class CustomerDAO extends AbstractDao<Customer>{
	private Connection con = this.connect;
	public static final String SELECT_ALL_SQL = "select * from oce_customer order by createdStamp DESC";	
	public static final String DELETE_SQL = "DELETE FROM oce_customer WHERE customer_id = ?";
	public static final String INSERT_SQL = "insert into oce_customer (customer_id,customer_name,remark,customer_discount,contact_id," +
			   "priceId,paymentTermId,taxSchemeId,taxexempt,paymentMethodId,carrierId,inventoryId,creditCardId,origin,source,customvalue1,customvalue2,customvalue3,customvalue4,customvalue5,customvalue6," +
			   "customvalue7,customvalue8,customvalue9,customvalue10) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String UPDATE_SQL = "UPDATE oce_customer SET customer_name = ?, remark = ?, customer_discount = ?, priceId = ?, paymentTermId = ?, taxSchemeId = ? , " +
	"taxexempt = ?, paymentMethodId = ? , carrierId = ?, inventoryId = ?, creditCardId = ?, customvalue1 = ?, customvalue2 = ? , customvalue3 = ?, customvalue4 = ?, customvalue5 = ?" +
	"WHERE customer_id = ? ";
	public static final String UPDATE_EXTRA_SQL = "UPDATE oce_customer SET paymentMethodId = ? , carrierId = ?, inventoryId = ?, creditCardId = ?, origin = ?, source = ? WHERE customer_id = ? ";
	
	public Customer create(Customer obj){
		try{			
			con.setAutoCommit(false);
			
			if(obj == null || obj.getId() == null || obj.getId().equals(""))
				return obj;
			
			if(!obj.getContact().getName().equals("") || !obj.getContact().getPhone().equals("") || !obj.getContact().getFax().equals("") 
					|| !obj.getContact().getEmail().equals("") || !obj.getContact().getWebsite().equals("")){
				ContactDAO dao = new ContactDAO();
				dao.create(obj.getContact());
			}
						
			
			PreparedStatement ps = con.prepareStatement(INSERT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			
			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, obj.getName());
			ps.setString(3, obj.getRemark());
			ps.setLong(4, new Long(obj.getDiscount()));
			if(obj.getContact().getId().equals("")){
				ps.setString(5, null);
			}else{
				ps.setLong(5,Long.parseLong(obj.getContact().getId()));
			}
			
			if(obj.getPrice() == null || obj.getPrice().getId().equals("")){
				ps.setString(6, null);
			}else{
				ps.setLong(6, Long.parseLong(obj.getPrice().getId()));
			}
			
			if(obj.getPaymentTerm()== null || obj.getPaymentTerm().getId().equals("")){
				ps.setString(7, null);
			}else{
				ps.setLong(7,Long.parseLong(obj.getPaymentTerm().getId()));
			}
			
			if(obj.getTaxingScheme()== null || obj.getTaxingScheme().getId().equals("")){
				ps.setString(8, null);
			}else{
				ps.setLong(8,Long.parseLong(obj.getTaxingScheme().getId()));
			}
			
			ps.setLong(9, obj.getTaxexempt());
			
			if(obj.getPaymentMethod()== null || obj.getPaymentMethod().getId().equals("")){
				ps.setLong(10, 0);
			}else{
				ps.setLong(10,Long.parseLong(obj.getPaymentMethod().getId()));
			}
			
			if(obj.getCarrier()== null || obj.getCarrier().getId().equals("")){
				ps.setLong(11, 0);
			}else{
				ps.setLong(11,Long.parseLong(obj.getCarrier().getId()));
			}

			if(obj.getInventory()== null || obj.getInventory().getId().equals("")){
				ps.setLong(12, 0);
			}else{
				ps.setLong(12,Long.parseLong(obj.getInventory().getId()));
			}
			
			if(obj.getCreditCard()== null || obj.getCreditCard().getId().equals("")){
				ps.setLong(13, 0);
			}else{
				ps.setLong(13,Long.parseLong(obj.getCreditCard().getId()));
			}
			
			if(obj.getOrigin() == null){
				ps.setDate(14, null);
			}else{
				ps.setDate(14, obj.getOrigin());
			}
			ps.setString(15, obj.getSource());
						
			int idx = 0;
			int databaseIdx = 16;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					ps.setString(databaseIdx,  obj.getCustoms().get(idx).getValue());
					idx++;
					databaseIdx++;
				}
			}
			
			for(int i = databaseIdx ; i< 26 ; i++){
				ps.setString(i, "");
			}
//			ps.setString(16, obj.getCustoms().get(0).getValue());
//			ps.setString(17, obj.getCustoms().get(1).getValue());
//			ps.setString(18, obj.getCustoms().get(2).getValue());
//			ps.setString(19, obj.getCustoms().get(3).getValue());
//			ps.setString(20, obj.getCustoms().get(4).getValue());
//			ps.setString(21, "");
//			ps.setString(22, "");
//			ps.setString(23, "");
//			ps.setString(24, "");
//			ps.setString(25, "");
			
			ConnectionDatabase.executeUpdate(ps);
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
            
            AddressDAO addDao = new AddressDAO();
			AddressOwnerDAO addOwnDao = new AddressOwnerDAO();
			
			for(Address add : obj.getAddresses()){
				if(!add.getAddress().equals("")){
					addDao.create(add);
					addOwnDao.create(new AddressOwner("1", obj.getId(), add.getId(), "Customer", add.getType()));
				}
			}
			
			//update custom
			CustomFieldDAO cusDao = new CustomFieldDAO();
			idx = 6;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					cus.setId(String.valueOf(idx));
					cusDao.update(cus);
					idx++;					
				}
			}
			
			for(int i = idx ; i< 11 ; i++){				
				cusDao.update(new CustomInfo("", "", String.valueOf(i), "Customer", false));
			}
			
			con.commit();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
		return obj;
	}
	
	public Customer update(Customer obj){
		try{
			con.setAutoCommit(false);
			
			ContactDAO dao = new ContactDAO();
			if(obj.getContact()!= null && obj.getContact().getId()!= null && !obj.getContact().getId().equals("")){				
				dao.update(obj.getContact());
			}else{
				if(!obj.getContact().getName().equals("") || !obj.getContact().getPhone().equals("") || !obj.getContact().getFax().equals("") 
						|| !obj.getContact().getEmail().equals("") || !obj.getContact().getWebsite().equals("")){
					dao.create(obj.getContact());
				}
			}
				
			
			PreparedStatement ps = con.prepareStatement(UPDATE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
						
			ps.setString(1, obj.getName());
			ps.setString(2, obj.getRemark());
			ps.setLong(3, new Long(obj.getDiscount()));

			if(obj.getPrice() == null || obj.getPrice().getId().equals("")){
				ps.setString(4, null);
			}else{
				ps.setLong(4, Long.parseLong(obj.getPrice().getId()));
			}
			
			if(obj.getPaymentTerm()== null || obj.getPaymentTerm().getId().equals("")){
				ps.setString(5, null);
			}else{
				ps.setLong(5,Long.parseLong(obj.getPaymentTerm().getId()));
			}
			
			if(obj.getTaxingScheme()== null || obj.getTaxingScheme().getId().equals("")){
				ps.setString(6, null);
			}else{
				ps.setLong(6,Long.parseLong(obj.getTaxingScheme().getId()));
			}
			
			ps.setLong(7, obj.getTaxexempt());
			
			if(obj.getPaymentMethod()== null || obj.getPaymentMethod().getId().equals("")){
				ps.setLong(8, 0);
			}else{
				ps.setLong(8,Long.parseLong(obj.getPaymentMethod().getId()));
			}
			
			if(obj.getCarrier()== null || obj.getCarrier().getId().equals("")){
				ps.setLong(9, 0);
			}else{
				ps.setLong(9,Long.parseLong(obj.getCarrier().getId()));
			}

			if(obj.getInventory()== null || obj.getInventory().getId().equals("")){
				ps.setLong(10, 0);
			}else{
				ps.setLong(10,Long.parseLong(obj.getInventory().getId()));
			}
			
			if(obj.getCreditCard()== null || obj.getCreditCard().getId().equals("")){
				ps.setLong(11, 0);
			}else{
				ps.setLong(11,Long.parseLong(obj.getCreditCard().getId()));
			}			
			
			int idx = 0;
			int databaseIdx = 12;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					ps.setString(databaseIdx,  obj.getCustoms().get(idx).getValue());
					idx++;
					databaseIdx++;
				}
			}
			
			for(int i = databaseIdx ; i< 17 ; i++){
				ps.setString(i, "");
			}
			
//			ps.setString(12, obj.getCustoms().get(0).getValue());
//			ps.setString(13, obj.getCustoms().get(1).getValue());
//			ps.setString(14, obj.getCustoms().get(2).getValue());
//			ps.setString(15, obj.getCustoms().get(3).getValue());
//			ps.setString(16, obj.getCustoms().get(4).getValue());
			
			ps.setLong(17, Long.parseLong(obj.getId()));
									
			ConnectionDatabase.executeUpdate(ps);			
			
			AddressDAO addDao = new AddressDAO();
			AddressOwnerDAO addOwnDao = new AddressOwnerDAO();
			
			for(Address add : obj.getAddresses()){
				if(!add.getId().equals("")){
					addDao.update(add);
					//addOwnDao.update(new AddressOwner("1", obj.getId(), add.getId(),"Vendor" , add.getType()));
				}else{
					if(add.getId().equals("") && !add.getAddress().equals("")){
						addDao.create(add);
						addOwnDao.create(new AddressOwner("1", obj.getId(), add.getId(),"Vendor" , add.getType()));
					}
				}
			}
			
			//update custom
			CustomFieldDAO cusDao = new CustomFieldDAO();
			idx = 6;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					cus.setId(String.valueOf(idx));
					cusDao.update(cus);
					idx++;					
				}
			}
			
			for(int i = idx ; i< 11 ; i++){				
				cusDao.update(new CustomInfo("", "", String.valueOf(i), "Customer", false));
			}
			
			con.commit();
			ps.close();
		}catch (Exception e) {
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
		return obj;
	}
	
	public Customer find(long id){
		Customer customer = new Customer();
		try{
			
		}catch (Exception e) {
			// TODO: handle exception
		}
		return customer;
	}	

	public void delete(Customer obj){
		try{
			con.setAutoCommit(false);
			
			if(obj.getContact() != null && !obj.getContact().getId().equals("")){					
				ContactDAO dao = new ContactDAO();
				dao.delete(obj.getContact());
			}
			
			PreparedStatement ps = con.prepareStatement(DELETE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,Long.parseLong(obj.getId()));
			
			ConnectionDatabase.executeUpdate(ps);	
			
			con.commit();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
	}
	
	public List<Customer> findAll(){
		List<Customer> customers = new ArrayList<Customer>();
		try{
			customers = convert(ConnectionDatabase.executeQuery(con.prepareStatement(SELECT_ALL_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
		}catch (Exception e) {
			// TODO: handle exception
			customers = null;
		}
		return customers;
	}
	
	private List<Customer> convert(ResultSet rs) throws SQLException {
		List<Customer> customers = new ArrayList<Customer>();
		ContactDAO conDao = new ContactDAO();
		PaymentTermDAO paymentDao = new PaymentTermDAO();
		TaxSchemeDAO taxDao = new TaxSchemeDAO();
		PriceCurrencyDAO priceCurrencyDao = new PriceCurrencyDAO();
		CarrierDAO carrierDao = new CarrierDAO();
		InventoryDAO invDao = new InventoryDAO();
		PaymentMethodDAO methodDao = new PaymentMethodDAO();
		AddressDAO addDao = new AddressDAO();
		CreditCardDAO cardDao = new CreditCardDAO();
		while(rs.next()){
			// Get the data from the row using the column index
	        String id = rs.getString(1);
	        String name = rs.getString(2);
	        String remark = rs.getString(3);
	        int discount = rs.getInt(4);
	        String contactId = rs.getString(5);
	        String priceId = rs.getString(6);
	        String paymentTermId = rs.getString(7);
	        String taxSchemeId = rs.getString(8);
	        int taxExmept = rs.getInt(9);
	        String paymentMethodId = rs.getString(10);
	        String carrierId = rs.getString(11);
	        String inventoryId = rs.getString(12);
	        String cardId = rs.getString(13);
	        Date origin = rs.getDate(14);
	        String source = rs.getString(15);
	        String customvalue1 = rs.getString(16);
	        String customvalue2 = rs.getString(17);
	        String customvalue3 = rs.getString(18);
	        String customvalue4 = rs.getString(19);
	        String customvalue5 = rs.getString(20);	  		         
	        
	        List<CustomInfo> cus = new ArrayList();
			cus.add(new CustomInfo("Alternate Contact", customvalue1,"","",true));
			cus.add(new CustomInfo("Alternate Contact2", customvalue2,"","",true));
			cus.add(new CustomInfo("Alternate Contact3", customvalue3,"","",true));
			cus.add(new CustomInfo("Alternate Contact4", customvalue4,"","",true));
			cus.add(new CustomInfo("Emergency Phone", customvalue5,"","",true));
			
			List<Address> adds = new ArrayList();
			adds = addDao.findByCustomerID(Long.parseLong(id));
			
			customers.add(new Customer(id, name, 0, remark, discount, adds , contactId == null ? null : conDao.find(Long.parseLong(contactId)), 
					priceId == null ? null : priceCurrencyDao.find(Long.parseLong(priceId)), 
					paymentTermId == null ? null : paymentDao.find(Long.parseLong(paymentTermId)),
					taxSchemeId == null ? null : taxDao.find(Long.parseLong(taxSchemeId)),taxExmept,
					paymentMethodId == null ? null : methodDao.find(Long.parseLong(paymentMethodId)),		
					carrierId == null ? null : carrierDao.find(Long.parseLong(carrierId)),
					inventoryId == null ? null : invDao.find(Long.parseLong(inventoryId)),
					cardId == null ? null : cardDao.find(Long.parseLong(cardId)),
        		    origin,source,		
        		    cus));
		}
		rs.close();
		return customers;
	}

	public Customer updateExtra(Customer obj) {
		try{
			con.setAutoCommit(false);
			
			CreditCardDAO dao = new CreditCardDAO();
			if(obj.getCreditCard()!= null && obj.getCreditCard().getId()!= null && !obj.getCreditCard().getId().equals("")){				
				dao.update(obj.getCreditCard());
			}else{
				if(!obj.getCreditCard().getCardType().equals("") || !obj.getCreditCard().getCardNumber().equals("") || !obj.getCreditCard().getExpirationTime().equals("") 
						|| !obj.getCreditCard().getSecurityCode().equals("")){
					dao.create(obj.getCreditCard());
				}
			}				
			
			PreparedStatement ps = con.prepareStatement(UPDATE_EXTRA_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
						
			if(obj.getPaymentMethod()== null || obj.getPaymentMethod().getId().equals("")){
				ps.setLong(1, 0);
			}else{
				ps.setLong(1,Long.parseLong(obj.getPaymentMethod().getId()));
			}
			
			if(obj.getCarrier()== null || obj.getCarrier().getId().equals("")){
				ps.setLong(2, 0);
			}else{
				ps.setLong(2,Long.parseLong(obj.getCarrier().getId()));
			}

			if(obj.getInventory()== null || obj.getInventory().getId().equals("")){
				ps.setLong(3, 0);
			}else{
				ps.setLong(3,Long.parseLong(obj.getInventory().getId()));
			}
			
			if(obj.getCreditCard()== null || obj.getCreditCard().getId().equals("")){
				ps.setLong(4, 0);
			}else{
				ps.setLong(4,Long.parseLong(obj.getCreditCard().getId()));
			}			
			
			ps.setDate(5, obj.getOrigin());
			ps.setString(6, obj.getSource());
			
			ps.setLong(7, Long.parseLong(obj.getId()));
									
			ConnectionDatabase.executeUpdate(ps);			
			
			con.commit();
			ps.close();
		}catch (Exception e) {
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
		return obj;		
	}
}
